﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//Thu Vien Them Vao
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace QuanLyQuanCafe.DataAccess
{
    class KetNoiDuLieu
    {
        private SqlConnection connect;
        private SqlDataAdapter dataAdapter;

        public KetNoiDuLieu()
        {
            KetNoi();
        }
        public void KetNoi()
        {
            string stringConnection = @"Data Source=.\SQLExpress;AttachDbFilename=D:\Course.IX\OOD\Project\QuanLyQuanCafeFolder\DataBase\QLCafé.mdf;Integrated Security=True";
            try
            {
                connect = new SqlConnection(stringConnection);
                connect.Open();
                connect.Close();
            }
            catch
            {
                MessageBox.Show("Lỗi Xảy Ra.\n", "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public DataTable LayDanhSachDuLieu(string sql)
        {
            DataTable dataTable = new DataTable();
            dataAdapter = new SqlDataAdapter(sql, connect);
            dataAdapter.Fill(dataTable);
            return dataTable;
        }

        public bool ExecuteQuery(string sql)
        {
            int numberRecordsEffect = 0;
            try
            {                
                if (connect.State == ConnectionState.Closed)
                    connect.Open();
                SqlCommand command = new SqlCommand(sql, connect);
                numberRecordsEffect = command.ExecuteNonQuery();
                if (connect.State == ConnectionState.Open)
                    connect.Close();
            }
            catch(Exception ex)
            {
                MessageBox.Show("Lỗi Xảy Ra.\n" + ex.Message, "Quản Lý Quán Cafe", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            if (numberRecordsEffect > 0)
                return true;
            else return false;
        }

        public string LayMaCuoiCung(string nameTable, string nameField)
        {
            string sql = "SELECT TOP 1  " + nameField + " FROM " + nameTable + " ORDER BY " + nameField + " DESC";
            DataTable dataTable = LayDanhSachDuLieu(sql);
            if (dataTable.Rows.Count == 1)
            {
                return dataTable.Rows[0][nameField].ToString();
            }
            else
                return "";
        }

        public bool KiemTraGiaTriTonTai(string nameTable, string nameField, string value)
        {
            string sql = "SELECT * FROM " + nameTable + " WHERE " + nameField + " = '" + value + "'";
            DataTable dataTable = LayDanhSachDuLieu(sql);
            if (dataTable.Rows.Count > 0)
                return true;
            return false;
        }

        public bool KiemTraGiaTriInt(string nameTable, string nameField, int value)
        {
            string sql = "SELECT * FROM " + nameTable + " WHERE " + nameField + " = '" + value + "'";
            DataTable dataTable = LayDanhSachDuLieu(sql);
            if (dataTable.Rows.Count > 0)
                return true;
            return false;
        }

    }
}
